"""
Case Type   : fullsql
Case Name   : track_stmt_stat_level参数set异常输入校验,取值范围:L0,L1,L2,OFF
Create At   : 2022/02/07
Owner       : zhao-pengcheng-g
Description :
    1.设置为OFF,OFF
    2.设置为小写off,off
    3.设置为小写l
    4.设置为大写L
    5.设置为取值范围以外的值
    6.设置为数字1,1
    7.设置分隔符为逗号以外的符号
    8.设置为Level1,Level2
    9.环境清理
Expect      :
    1.设设置成功,系统表不记录任何信息
    2.设置成功
    3.设置成功
    4.设置成功
    5.合理报错
    6.合理报错
    7.合理报错
    8.合理报错
    9.环境清理成功
History     :
"""

import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Case0025 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_name = 't_fullsql_0025'

    def test_fullsql(self):
        text = '---step1:设置为OFF,OFF,expect:设置成功,系统表不记录任何信息---'
        self.logger.info(text)
        sql_cmd = f'''show track_stmt_stat_level;
                      set track_stmt_stat_level='OFF,OFF';
                      show track_stmt_stat_level;
                      set log_min_duration_statement=0;
                      drop table if exists {self.table_name};
                      create table {self.table_name}( a int,b int);
                      select pg_sleep(60)'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.level_default = sql_msg.splitlines()[2].strip()
        assert_set = sql_msg.count(self.constant.SET_SUCCESS_MSG) == 2
        self.assertIn(assert_set and 'OFF,OFF' and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS,
                      sql_msg, '执行失败:' + text)
        select_cmd = f"select * from statement_history " \
                     f"where query like '%{self.table_name}%';"
        select_msg = self.sh_primy.execut_db_sql(select_cmd, dbname='postgres')
        self.logger.info(select_msg)
        self.assertIn('(0 rows)', select_msg, '执行失败:' + text)

        text = "---step2:设置为小写off,expect:设置成功---"
        self.logger.info(text)
        sql_cmd = f"set track_stmt_stat_level='off,off';" \
                  f"show track_stmt_stat_level;"
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn(self.constant.SET_SUCCESS_MSG and 'off,off',
                      sql_msg, '执行失败:' + text)

        text = '---step3:设置为小写l,expect:设置成功---'
        self.logger.info(text)
        sql_cmd = f'''set track_stmt_stat_level='l0,l0';
                      show track_stmt_stat_level;
                      set track_stmt_stat_level='l1,l1';
                      show track_stmt_stat_level;
                      set track_stmt_stat_level='l2,l2';
                      show track_stmt_stat_level;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn(self.constant.SET_SUCCESS_MSG,
                      sql_msg.splitlines()[0], '执行失败:' + text)
        self.assertIn('l0,l0', sql_msg.splitlines()[3], '执行失败:' + text)
        self.assertIn(self.constant.SET_SUCCESS_MSG,
                      sql_msg.splitlines()[6], '执行失败:' + text)
        self.assertIn('l1,l1', sql_msg.splitlines()[9], '执行失败:' + text)
        self.assertIn(self.constant.SET_SUCCESS_MSG,
                      sql_msg.splitlines()[12], '执行失败:' + text)
        self.assertIn('l2,l2', sql_msg.splitlines()[15], '执行失败:' + text)

        text = '---step4:设置为大写L,expect:设置成功---'
        self.logger.info(text)
        sql_cmd = f'''set track_stmt_stat_level='L0,L0';
                    show track_stmt_stat_level;
                    set track_stmt_stat_level='L1,L1';
                    show track_stmt_stat_level;
                    set track_stmt_stat_level='L2,L2';
                    show track_stmt_stat_level;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn(self.constant.SET_SUCCESS_MSG,
                      sql_msg.splitlines()[0], '执行失败:' + text)
        self.assertIn('L0,L0', sql_msg.splitlines()[3], '执行失败:' + text)
        self.assertIn(self.constant.SET_SUCCESS_MSG,
                      sql_msg.splitlines()[6], '执行失败:' + text)
        self.assertIn('L1,L1', sql_msg.splitlines()[9], '执行失败:' + text)
        self.assertIn(self.constant.SET_SUCCESS_MSG,
                      sql_msg.splitlines()[12], '执行失败:' + text)
        self.assertIn('L2,L2', sql_msg.splitlines()[15], '执行失败:' + text)

        text = "---step5:设置为取值范围以外的值,expect:合理报错---"
        self.logger.info(text)
        sql_cmd = f'''set track_stmt_stat_level='L3,L3';
                      show track_stmt_stat_level;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn(self.constant.invalid_value_msg and self.level_default,
                      sql_msg, '执行失败:' + text)

        text = "---step6:设置为数字1,1,expect:合理报错---"
        self.logger.info(text)
        sql_cmd = f'''set track_stmt_stat_level='1,1';
                      show track_stmt_stat_level;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn(self.constant.invalid_value_msg and self.level_default,
                      sql_msg, '执行失败:' + text)

        text = "---step7:设置分隔符为逗号以外的符号,expect:合理报错---"
        self.logger.info(text)
        sql_cmd = f'''set track_stmt_stat_level='L2;L2';
                      show track_stmt_stat_level;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn(self.constant.invalid_value_msg and self.level_default,
                      sql_msg, '执行失败:' + text)

        text = "---step8:设置为Level1,Level2,expect:合理报错---"
        self.logger.info(text)
        sql_cmd = f'''set track_stmt_stat_level='Level1,Level2';
                    show track_stmt_stat_level;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn(self.constant.invalid_value_msg and self.level_default,
                      sql_msg, '执行失败:' + text)

    def tearDown(self):
        text = '---step9:环境清理，expect:成功---'
        self.logger.info(text)
        drop_cmd = f"drop table {self.table_name};"
        drop_msg = self.sh_primy.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      drop_msg, '执行失败:' + text)
        self.logger.info(
            '---Opengauss_Function_Fullsql_Case0025 finish---')
